﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using ClosedXML.Excel;
using Dapper;
using FinHist.Data;

namespace FinHist.StockDownloader
{
    class Program
    {
        static void Main()
        {
            var files = new Dictionary<string, string> { { "SS", @"ss.a.xlsx" }, { "SZ", @"sz.a.xlsx" } };

            Console.WriteLine("Reading...");

            var stocks = files
                .AsParallel()
                .SelectMany(p =>
                {
                    var path = System.IO.Path.Combine(Thread.GetDomain().BaseDirectory, p.Value);
                    using (var workbook = new XLWorkbook(path))
                    {
                        return workbook.Worksheet(1)
                            .Rows()
                            .Select(row => new Stock
                                {
                                    code = row.Cell("A").Value.ToString().ToUpper(),
                                    exchange = p.Key.ToUpper(),
                                    companyName = row.Cell("B").Value.ToString(),
                                    yahooId = row.Cell("A").Value.ToString().ToUpper() + "." + p.Key.ToUpper()
                                })
                            .Where(p1 => p1 != null && !string.IsNullOrEmpty(p1.code) && Regex.IsMatch(p1.code, @"\d+"))
                            .ToArray();
                    }
                });

            Console.WriteLine("Total {0}", stocks.Count());

            var sqlInsertClauses = stocks
                .Select(p =>
                    string.Format("SELECT '{0}','{1}','{2}','{3}'",
                    p.code, p.exchange, p.companyName, p.yahooId));

            var sql =
                @"declare @temp table
(
code nvarchar(100),
exchange nvarchar(100),
companyName nvarchar(512),
yahooId nvarchar(100),
PRIMARY KEY CLUSTERED(code, exchange)
)
INSERT INTO @temp(code,exchange,companyName,yahooId)" + Environment.NewLine +
                string.Join(" UNION ALL " + Environment.NewLine, sqlInsertClauses) + Environment.NewLine +
                @"MERGE INTO stock
     USING @temp p
          ON p.code = stock.code and p.exchange = stock.exchange
WHEN MATCHED THEN
     UPDATE SET stock.companyName = p.companyName,stock.yahooId = p.yahooId
WHEN NOT MATCHED THEN
     INSERT (code,exchange,companyName, yahooId) VALUES(p.code,p.exchange,p.companyName, p.yahooId);";

            Console.WriteLine("Saving db...");

            var connString = ConfigurationManager.ConnectionStrings["trading"].ConnectionString;
            using (var conn = new SqlConnection(connString))
            {
                conn.Open();

                conn.Execute(sql);
            }
        }
    }
}
